package fu.capstone.hanoicityguide.dataaccess.home;

import java.util.ArrayList;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import fu.capstone.hanoicityguide.R;
import fu.capstone.hanoicityguide.business.model.Category;
import fu.capstone.hanoicityguide.business.model.Home;
import fu.capstone.hanoicityguide.business.model.Place;
import fu.capstone.hanoicityguide.dataaccess.base.BaseDAOImpl;
import fu.capstone.hanoicityguide.dataaccess.base.DatabaseHelper;

public class HomeDAOImp extends BaseDAOImpl implements HomeDAO {
	private SQLiteDatabase database;
	private DatabaseHelper dbHelper;
	private Context context;

	public HomeDAOImp(Context ctx) {
		super(ctx);
		this.context = ctx;
	}

	@Override
	public Home browseHome() {
		Cursor cursor = null;
		Home home = new Home();
		ArrayList<String> arrImage = new ArrayList<String>();
		try {
			dbHelper = connectDB();
			database = dbHelper.getReadableDatabase();
			String queryHome = "SELECT Home.[Overview] , Home.[History] , AlbumImage.[ImagePath1] , AlbumImage.[ImagePath2] , AlbumImage.[ImagePath3] , AlbumImage.[ImagePath4] , AlbumImage.[ImagePath5]  FROM [Home] LEFT JOIN [AlbumImage] ON Home.[AlbumImageId] = AlbumImage.[Id] ";
			cursor = database.rawQuery(queryHome, null);
			if (cursor != null) {
				if (cursor.moveToFirst()) {
					do {
						home.setOverview(cursor.getString(cursor
								.getColumnIndex("Overview")));
						home.setHistory(cursor.getString(cursor
								.getColumnIndex("History")));

						arrImage.add(cursor.getString(cursor
								.getColumnIndex("ImagePath1")));
						arrImage.add(cursor.getString(cursor
								.getColumnIndex("ImagePath2")));
						arrImage.add(cursor.getString(cursor
								.getColumnIndex("ImagePath3")));
						arrImage.add(cursor.getString(cursor
								.getColumnIndex("ImagePath4")));
						arrImage.add(cursor.getString(cursor
								.getColumnIndex("ImagePath5")));

						home.setArrImage(arrImage);
					} while (cursor.moveToNext());

				}
			}
		} catch (Exception e) {
			// TODO: handle exception
		} finally {
			closeDB(database, dbHelper);
		}
		return home;
	}

	@Override
	public ArrayList<Place> searchPlace(String keyword,
			ArrayList<String> filter, String sorter) {
		Cursor cursor = null;
		ArrayList<Place> arrResult = new ArrayList<Place>();
		try {
			dbHelper = connectDB();
			database = dbHelper.getReadableDatabase();
			String queryPlace = "SELECT Place.[Id] , Place.[Name] , "
					+ "Category.[CategoryName] , Type.[TypeName] , Rating.[RatingValue] , Place.[Price] ,"
					+ " AlbumImage.[IconPath] FROM Place "
					+ "LEFT JOIN Geolocation ON Place.[GeoId] = Geolocation.[Id] "
					+ "LEFT JOIN Category ON Place.[CategoryId] = Category.[Id] "
					+ "LEFT JOIN Type ON Place.[TypeId] = Type.[Id] "
					+ "LEFT JOIN Rating ON Place.[RatingId] = Rating.[Id] "
					+ "LEFT JOIN AlbumImage ON Place.[AlbumImageId] = AlbumImage.[Id] ";

			// filter by keyword
			queryPlace = queryPlace + " WHERE Place.[Name] LIKE '%" + keyword
					+ "%' ";

			// filter
			if (filter.size() > 0) {
				queryPlace = queryPlace
						+ " AND ( Category.[CategoryName] == \""
						+ filter.get(0) + "\"";
				for (int i = 1; i < filter.size(); i++) {
					queryPlace = queryPlace
							+ " OR Category.[CategoryName] == \""
							+ filter.get(i) + "\"";
				}
				queryPlace = queryPlace + " ) ";
			} else {
				queryPlace = queryPlace
						+ " AND Category.[CategoryName] == \"\"";
			}

			// sort
			if (sorter.equals(context.getResources().getStringArray(
					R.array.sorter_items)[0])) {
				queryPlace = queryPlace + " ORDER BY Rating.[RatingValue] DESC";
			} else if (sorter.equals(context.getResources().getStringArray(
					R.array.sorter_items)[1])) {
				queryPlace = queryPlace + " ORDER BY Rating.[RatingValue] ASC";
			} else if (sorter.equals(context.getResources().getStringArray(
					R.array.sorter_items)[2])) {
				queryPlace = queryPlace + " ORDER BY Place.[Name] DESC";
			} else if (sorter.equals(context.getResources().getStringArray(
					R.array.sorter_items)[3])) {
				queryPlace = queryPlace + " ORDER BY Place.[Name] ASC";
			} else {
			}

			// execute query
			cursor = database.rawQuery(queryPlace, null);

			if (cursor != null) {
				if (cursor.moveToFirst()) {
					do {
						Place place = new Place();
						place.setId(cursor.getInt(cursor.getColumnIndex("Id")));
						place.setName(cursor.getString(cursor
								.getColumnIndex("Name")));
						place.setQuality(cursor.getInt(cursor
								.getColumnIndex("RatingValue")));
						place.setCategory(cursor.getString(cursor
								.getColumnIndex("CategoryName")));
						place.setType(cursor.getString(cursor
								.getColumnIndex("TypeName")));
						place.setIconImage(cursor.getString(cursor
								.getColumnIndex("IconPath")));

						arrResult.add(place);
					} while (cursor.moveToNext());
				}
			}
		} catch (Exception e) {
			Log.v("searchHome : Can't query DB", e.toString());
		} finally {
			closeDB(database, dbHelper);
		}
		return arrResult;
	}

	@Override
	public ArrayList<Category> getListCategory(String filter) {
		// TODO Auto-generated method stub
		return super.getListCategory(filter);
	}

}
